const tool = require('../tools/tool')

module.exports = {
    async home() {
        let re = await tool.dbf().exe(`SELECT status, COUNT(*) AS count
        FROM patrols
        WHERE status IN (1, 2, 3, 4, 5)
        GROUP BY status;`)
        let res = {}
        await tool.array.format(re, async (item) => {
            res[item.status] = item.count
        })

        let re_user = await tool.dbf().exe(`select role, count(*) as count from users where role in ('老师','学生') group by role`)

        return {
            patrol_status: res,
            patrol_total: (await tool.dbf().select(`select count(*) as total from patrols`))[0].total,
            user_role: await (async () => {
                let re = {}
                await tool.array.format(re_user, (item) => {
                    re[item.role] = item.count
                })
                return re
            })(),

        }
    },
    patrol: {
        async total() {
            let re = await tool.dbf().exe(`SELECT status, COUNT(*) AS count
FROM patrols
WHERE status IN (1, 2, 3, 4, 5)
GROUP BY status;`)
            let total = (await tool.dbf().select(`select count(*) as total from patrols`))[0].total
            let res = {
                '1': 0,
                '2': 0,
                '3': 0,
                '4': 0,
                '5': 0,
            }
            await tool.array.format(re, async (item) => {
                res[item.status] = item.count
            })
            // console.log(res)
            return {
                title: '全部巡查各状态数量饼图',
                tip: `当前系统中共有${(await tool.dbf().select(`select count(*) as total from patrols`))[0].total}个巡查。下图表示系统中全部巡查每种状态占总数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: [
                                { value: res['1'], name: `等待(${tool.formatPercentNum(res['1'] / total)}%)`, itemStyle: { color: 'rgb(0,120,215)' } },
                                { value: res['2'], name: `延期(${tool.formatPercentNum(res['2'] / total)}%)`, itemStyle: { color: '#7100ac' } },
                                { value: res['3'], name: `满意度调查中(${tool.formatPercentNum(res['3'] / total)}%)`, itemStyle: { color: '#01bd40' } },
                                { value: res['4'], name: `完成(${tool.formatPercentNum(res['4'] / total)}%)`, itemStyle: { color: '#686868' } },
                                { value: res['5'], name: `拒绝(${tool.formatPercentNum(res['5'] / total)}%)`, itemStyle: { color: '#ff1d77' } }
                            ],
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async finished() {
            let re = await tool.dbf().exe(`SELECT status, COUNT(*) AS count
        FROM patrols
        WHERE status IN (4, 5)
        GROUP BY status;`)
            let total = (await tool.dbf().select(`select count(*) as total from patrols where status = 4 or status = 5`))[0].total
            let res = {
                '4': 0,
                '5': 0,
            }
            await tool.array.format(re, async (item) => {
                res[item.status] = item.count
            })
            // console.log(res)
            return {
                title: '流程结束巡查各状态数量饼图',
                tip: `当前系统中共有${(await tool.dbf().select(`select count(*) as total from patrols where status = 4 or status = 5`))[0].total}个流程结束巡查。下图表示系统中流程结束的巡查每种状态占流程结束巡查数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: [
                                { value: res['4'], name: `完成(${tool.formatPercentNum(res['4'] / total)}%)`, itemStyle: { color: '#686868' } },
                                { value: res['5'], name: `拒绝(${tool.formatPercentNum(res['5'] / total)}%)`, itemStyle: { color: '#ff1d77' } }
                            ],
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async preToDo() {
            let re = await tool.dbf().exe(`SELECT status, COUNT(*) AS count
        FROM patrols
        WHERE status IN (1, 2, 3)
        GROUP BY status;`)
            let total = (await tool.dbf().select(`select count(*) as total from patrols where status = 1 or status = 2 or status = 3`))[0].total
            let res = {
                '1': 0,
                '2': 0,
                '3': 0
            }
            await tool.array.format(re, async (item) => {
                res[item.status] = item.count
            })
            // console.log(res)
            return {
                title: '未完成巡查各状态数量饼图',
                tip: `当前系统中共有${(await tool.dbf().select(`select count(*) as total from patrols where status = 1 or status = 2 or status = 3`))[0].total}个未完成的巡查。下图表示系统中未完成的巡查每种状态占未完成巡查数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: [
                                { value: res['1'], name: `等待(${tool.formatPercentNum(res['1'] / total)}%)`, itemStyle: { color: 'rgb(0,120,215)' } },
                                { value: res['2'], name: `延期(${tool.formatPercentNum(res['2'] / total)}%)`, itemStyle: { color: '#7100ac' } },
                                { value: res['3'], name: `满意度调查中(${tool.formatPercentNum(res['3'] / total)}%)`, itemStyle: { color: '#01bd40' } },
                            ],
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async campus() {
            let total = await tool.methods.patrol().total()
            let campus = await tool.methods.campuses().getList()
            campus = campus.default
            for (let i = 0; i < campus.length; i++) {
                campus[i].num = (await tool.dbf().select(`select count(*) as total from patrols where campusId = ${campus[i].id}`))[0].total
                campus[i].name = `${campus[i].name}(${tool.formatPercentNum(campus[i].num / total)}%)`

                campus[i] = {
                    value: campus[i].num,
                    name: campus[i].name
                }
            }
            return {
                title: '各校区巡查数量饼图',
                tip: `当前系统中共有${(await tool.dbf().select(`select count(*) as total from patrols`))[0].total}个巡查。下图表示系统中各校区巡查数量占巡查总数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: campus,
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async category() {
            let total = await tool.methods.patrol().total()
            let campus = await tool.methods.categories().getList()
            campus = campus.default
            for (let i = 0; i < campus.length; i++) {
                campus[i].num = (await tool.dbf().select(`select count(*) as total from patrols where categoryId = ${campus[i].id}`))[0].total
                campus[i].name = `${campus[i].name}(${tool.formatPercentNum(campus[i].num / total)}%)`

                campus[i] = {
                    value: campus[i].num,
                    name: campus[i].name
                }
            }
            return {
                title: '各类别巡查数量饼图',
                tip: `当前系统中共有${(await tool.dbf().select(`select count(*) as total from patrols`))[0].total}个巡查。下图表示系统中各校类别巡查数量占巡查总数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: campus,
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async campusCategory(campusId, categoryId) {
            let total = (await tool.dbf().select(`select count(*) as total from patrols where campusId = ${campusId} and categoryId = ${categoryId}`))[0].total
            let res = []
            for (let i = 1; i < 6; i++) {
                let cn = (await tool.dbf().select(`select count(*) as total from patrols where campusId = ${campusId} and categoryId = ${categoryId} and status = ${i}`))[0].total
                res.push({
                    value: cn,
                    name: `${(() => {
                        if (i == 1) return '等待'
                        if (i == 2) return '延期'
                        if (i == 3) return '满意度调查中'
                        if (i == 4) return '完成'
                        if (i == 5) return '拒绝'
                    })()}(${tool.formatPercentNum(cn / total)}%)`,
                    itemStyle: {
                        color: (() => {
                            if (i == 1) return 'rgb(0,120,215)'
                            if (i == 2) return '#7100ac'
                            if (i == 3) return '#01bd40'
                            if (i == 4) return '#686868'
                            if (i == 5) return '#ff1d77'
                        })()
                    }
                })
            }
            return {
                title: `校区类别巡查各状态数量饼图`,
                tip: `${(await tool.methods.campuses().getInfo(campusId)).name}-${(await tool.methods.categories().getInfo(categoryId)).name}中共有${total}个巡查。下图表示${(await tool.methods.campuses().getInfo(campusId)).name}-${(await tool.methods.categories().getInfo(categoryId)).name}中各状态巡查数量占当前校区类别巡查总数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: res,
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async user(userId) {
            let userData = await tool.methods.user().getSingleUserInfoByUserId(userId, true)
            let total = (await tool.dbf().select(`select count(*) as total from patrols where userId = ${userId}`))[0].total
            if (!total) throw '此用户未新建过巡查'
            let res = []
            for (let i = 1; i < 6; i++) {
                let cn = (await tool.dbf().select(`select count(*) as total from patrols where userId = ${userId} and status = ${i}`))[0].total
                res.push({
                    value: cn,
                    name: `${(() => {
                        if (i == 1) return '等待'
                        if (i == 2) return '延期'
                        if (i == 3) return '满意度调查中'
                        if (i == 4) return '完成'
                        if (i == 5) return '拒绝'
                    })()}(${tool.formatPercentNum(cn / total)}%)`,
                    itemStyle: {
                        color: (() => {
                            if (i == 1) return 'rgb(0,120,215)'
                            if (i == 2) return '#7100ac'
                            if (i == 3) return '#01bd40'
                            if (i == 4) return '#686868'
                            if (i == 5) return '#ff1d77'
                        })()
                    }
                })
            }
            return {
                title: `用户巡查各状态数量饼图`,
                tip: `${userData.username}总共新建了${total}个巡查。下图表示${userData.username}新建的巡查中各状态巡查数量占当前${userData.username}新建的巡查总数的比例。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: res,
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async handler(userId) {
            let userData = await tool.methods.user().getSingleUserInfoByUserId(userId, true)
            let re = await tool.dbf().exe(`WITH DeduplicatedFeedback AS (
    SELECT DISTINCT patrolId, createdAt, rating
    FROM patrols_feedback
),
LatestFeedback AS (
    SELECT patrolId, MAX(createdAt) AS latestFeedback
    FROM DeduplicatedFeedback
    GROUP BY patrolId
),
AllRatings AS (
    SELECT 1 AS rating UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
),
RatingCounts AS (
    SELECT 
        pf.rating, 
        COUNT(*) AS rating_count
    FROM 
        patrols_handle ph
    JOIN 
        LatestFeedback latest_pf ON ph.patrolId = latest_pf.patrolId
    JOIN 
        DeduplicatedFeedback pf ON latest_pf.patrolId = pf.patrolId AND latest_pf.latestFeedback = pf.createdAt
    JOIN 
        patrols p ON ph.patrolId = p.id
    WHERE 
        ph.userId = ${userId} 
        AND p.status = 4 
        AND pf.createdAt > ph.createdAt
    GROUP BY 
        pf.rating
)
SELECT 
    ar.rating, 
    COALESCE(rc.rating_count, 0) AS rating_count
FROM 
    AllRatings ar
LEFT JOIN RatingCounts rc ON ar.rating = rc.rating
ORDER BY 
    ar.rating;


`)
            let total = 0
            let res = []
            for (let i = 0; i < re.length; i++) {
                total += re[i].rating_count
            }
            for (let i = 0; i < re.length; i++) {
                res.push({
                    value: re[i].rating_count,
                    name: `${(() => {
                        if (re[i].rating == 1) return '非常满意'
                        if (re[i].rating == 2) return '满意'
                        if (re[i].rating == 3) return '一般'
                        if (re[i].rating == 4) return '不满意'
                        if (re[i].rating == 5) return '非常不满意'
                    })()}(${tool.formatPercentNum(re[i].rating_count / total)}%)`
                })
            }
            if (!total) throw '此用户没有处理且被评价的巡查'
            return {
                title: `负责人处理巡查评价饼图`,
                tip: `${userData.username}总共处理的全部巡查中已被巡查人评价的有${total}个。下图表示${userData.username}处理且被评价的巡查中各评分数量占当前巡查总数的比例。此数据不区分校区类别，统计用户处理过的且被巡查人评价的全部巡查。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: res,
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async handlerReview(userId) {
            let userData = await tool.methods.user().getSingleUserInfoByUserId(userId, true)
            let re = await tool.dbf().exe(`
    SELECT 
        ph.userId,
        COUNT(DISTINCT ph.patrolId) AS total_patrols,
        COUNT(DISTINCT CASE WHEN pr.id IS NOT NULL THEN ph.patrolId END) AS rejected,
        COUNT(DISTINCT CASE WHEN pr.id IS NULL THEN ph.patrolId END) AS not_rejected
    FROM 
        patrols_handle ph
    LEFT JOIN 
        (SELECT 
             r1.* 
         FROM 
             patrols_review r1
         LEFT JOIN 
             patrols_handle h1 ON r1.patrolId = h1.patrolId 
                AND r1.createdAt > h1.updatedAt 
                AND h1.userId = ${userId} 
                AND NOT EXISTS (SELECT 1 
                                FROM patrols_handle h2 
                                WHERE h2.patrolId = r1.patrolId 
                                  AND h2.updatedAt BETWEEN h1.updatedAt AND r1.createdAt
                                  AND h2.userId <> ${userId})) pr 
    ON 
        ph.patrolId = pr.patrolId 
        AND pr.userId <> ph.userId
    WHERE 
        ph.userId = ${userId}
    GROUP BY 
        ph.userId;
    `)
            re = re[0]
            if (!re) throw '此用户没有处理过巡查'
            let total = re.rejected + re.not_rejected
            if (!total) throw '此用户没有处理过巡查'
            return {
                title: `负责人处理巡查审核情况饼图`,
                tip: `${userData.username}处理过的巡查有${total}个。下图表示${userData.username}处理的巡查中被驳回和未被驳回的巡查占当前巡查总数的比例。此数据不区分校区类别。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: [
                                {
                                    value: re.rejected,
                                    name: `被驳回(${tool.formatPercentNum(re.rejected / total)}%)`
                                },
                                {
                                    value: re.not_rejected,
                                    name: `未驳回(${tool.formatPercentNum(re.not_rejected / total)}%)`
                                },
                            ],
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        },
        async handlerEndTime(userId) {
            let userData = await tool.methods.user().getSingleUserInfoByUserId(userId, true)
            let re = await tool.dbf().exe(`

WITH FirstHandle AS (
    SELECT 
        patrolId,
        MIN(createdAt) AS firstHandleTime
    FROM 
        patrols_handle
    GROUP BY 
        patrolId
)

SELECT 
    ph.userId,
    COUNT(CASE WHEN p.endTime < ph.createdAt THEN 1 END) AS overdue,
    COUNT(CASE WHEN p.endTime >= ph.createdAt THEN 1 END) AS ontime
FROM 
    patrols_handle ph
JOIN 
    FirstHandle fh ON ph.patrolId = fh.patrolId AND ph.createdAt = fh.firstHandleTime
JOIN 
    patrols p ON ph.patrolId = p.id
WHERE 
    ph.userId = ${userId}
GROUP BY 
    ph.userId;`)
            re = re[0]
            if (!re) throw '此用户未处理过巡查'
            let total = re.overdue + re.ontime
            if (!total) throw '此用户没有处理过巡查'
            let re2 = await tool.dbf().exe(`WITH FirstHandle AS (
    SELECT 
        patrolId,
        userId,
        MIN(createdAt) AS firstHandleTime
    FROM 
        patrols_handle
    GROUP BY 
        patrolId, userId
)

, HandleTimes AS (
    SELECT
        ph.patrolId,
        ph.userId,
        p.endTime,
        ph.createdAt AS handleTime,
        TIMESTAMPDIFF(SECOND, p.createdAt, ph.createdAt) AS timeTaken
    FROM 
        patrols_handle ph
    JOIN 
        FirstHandle fh ON ph.patrolId = fh.patrolId AND ph.createdAt = fh.firstHandleTime AND ph.userId = fh.userId
    JOIN 
        patrols p ON ph.patrolId = p.id
    WHERE 
        ph.userId = ${userId}
)

SELECT 
    userId,
    ROUND(SUM(CASE WHEN endTime < handleTime THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS overdue_rate_percentage,
    AVG(timeTaken) AS average_time_seconds
FROM 
    HandleTimes
GROUP BY 
    userId;`)
            re2 = re2[0]
            if (!re2) throw '此用户未处理过巡查'

            return {
                title: `负责人处理巡查用时情况饼图`,
                tip: `${userData.username}处理过的巡查有${total}个。平均处理用时${tool.formatTimeDifference(re2.average_time_seconds*1000, 0)}。下图表示${userData.username}处理的巡查中按时处理和超时处理巡查占当前巡查总数的比例。此数据不区分校区类别。`,
                options: {
                    tooltip: {
                        trigger: 'item'
                    },
                    legend: {
                        orient: 'vertical',
                        left: 'left'
                    },
                    series: [
                        {
                            type: 'pie',
                            radius: '50%',
                            data: [
                                {
                                    value: re.overdue,
                                    name: `超时(${tool.formatPercentNum(re.overdue / total)}%)`
                                },
                                {
                                    value: re.ontime,
                                    name: `按时(${tool.formatPercentNum(re.ontime / total)}%)`
                                },
                            ],
                            emphasis: {
                                itemStyle: {
                                    shadowBlur: 10,
                                    shadowOffsetX: 0,
                                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}